package com.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DBUtil {
	private static List<?> resList;
	private static Object object;

	public static void setNullObject(Connection con, PreparedStatement pStmt,
			ResultSet rs) {
		try {

			if (con != null) {
				con.close();
				con = null;
			}

			if (pStmt != null) {
				pStmt.close();
				pStmt = null;
			}

			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	private static Connection getDBConnection(ComboPooledDataSource dataSource)
			throws SQLException {
		return dataSource.getConnection();
	}

	private static PreparedStatement getStatement(String sql, Connection con)
			throws SQLException {
		return con.prepareStatement(sql);
	}

	private static boolean isFlag(ResultSet rs) throws SQLException {
		boolean flag = false;
		if (rs.next()) {
			flag = true;
		}
		return flag;
	}

	private static ResultSet getResultSet(PreparedStatement pStmt)
			throws SQLException {
		return pStmt.executeQuery();
	}

	private static void executeUpdate(String sql, PreparedStatement pStmt)
			throws SQLException {
		if (sql != null && !sql.equals("")) {
			pStmt.executeUpdate(sql);
		} else {
			pStmt.executeUpdate();
		}

	}

	private static void commitConnection(Connection con) throws SQLException {
		con.setAutoCommit(false);
		con.commit();
	}

	public static void addOperation(ComboPooledDataSource dataSource, String sql)
			throws Exception {
		Connection con = null;
		PreparedStatement pStmt = null;

		try {
			con = getDBConnection(dataSource);

			pStmt = getStatement(sql, con);
			executeUpdate(null, pStmt);

			commitConnection(con);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				setNullObject(con, pStmt, null);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	public static List getCollectionOperation(ComboPooledDataSource dataSource,
			String sql, DataGeneration dg) throws Exception {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = getDBConnection(dataSource);

			pStmt = getStatement(sql, con);
			rs = getResultSet(pStmt);

			resList = dg.getResList(rs);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return resList;

	}

	public static Object getObjectOperation(ComboPooledDataSource dataSource,
			String sql, DataGeneration dg) throws Exception {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = getDBConnection(dataSource);

			pStmt = getStatement(sql, con);
			rs = getResultSet(pStmt);

			object = dg.getResModel(rs);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return object;
	}

	public static boolean getBooleanOperation(ComboPooledDataSource dataSource,
			String sql) throws Exception {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		boolean flag = false;

		try {
			con = getDBConnection(dataSource);

			pStmt = getStatement(sql, con);
			rs = getResultSet(pStmt);
			flag = isFlag(rs);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return flag;
	}

	public static void editOrDeleteOperation(ComboPooledDataSource dataSource,
			String sql) throws Exception {
		Connection con = null;
		PreparedStatement pStmt = null;

		try {
			con = getDBConnection(dataSource);
			pStmt = getStatement(sql, con);

			executeUpdate(sql, pStmt);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				setNullObject(con, pStmt, null);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}
}
